package com.shlh.saas.mapper.company;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shlh.saas.entity.company.Company;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 全球企业库数据访问接口
 */
@Mapper
public interface CompanyMapper extends BaseMapper<Company> {

    /**
     * 查询所有全球企业库数据
     */
    List<Company> selectAll();

    /**
     * 根据条件查询全球企业库数据
     */
    List<Company> searchByCondition(@Param("params") Map<String, Object> params);

    /**
     * 在所有字段中搜索关键词（带分页）
     */
    List<Company> searchInAllFieldsWithPagination(
            @Param("keyword") String keyword,
            @Param("offset") int offset,
            @Param("limit") int limit
    );

    /**
     * 统计在所有字段中包含关键词的记录总数
     */
    long countInAllFields(@Param("keyword") String keyword);

    /**
     * 查询所有数据（带分页）
     */
    List<Company> selectAllWithPagination(
            @Param("offset") int offset,
            @Param("limit") int limit
    );
    
    /**
     * 分页查询全球企业库数据
     */
    @Select("SELECT * FROM companies ORDER BY id LIMIT #{limit} OFFSET #{offset}")
    List<Company> selectPage(@Param("offset") int offset, @Param("limit") int limit);

    /**
     * 统计总记录数
     */
    long countAll();
    
    /**
     * 统计记录总数
     */
    @Select("SELECT COUNT(1) FROM companies")
    long selectCount(@Param("ew") Object wrapper);

    /**
     * 查找相似的公司名称
     */
    List<String> findSimilarCompanyNames(@Param("keyword") String keyword);

    /**
     * 查找相似的行业
     */
    List<String> findSimilarIndustries(@Param("keyword") String keyword);
    
    /**
     * 根据条件分页查询全球企业库数据
     */
    @Select("<script>" +
            "SELECT * FROM companies " +
            "<where> " +
            "<if test='params.companyName != null'> " +
            "AND company_name LIKE #{params.companyName} " +
            "</if> " +
            "<if test='params.firstName != null'> " +
            "AND first_name LIKE #{params.firstName} " +
            "</if> " +
            "<if test='params.lastName != null'> " +
            "AND last_name LIKE #{params.lastName} " +
            "</if> " +
            "<if test='params.countries != null and params.countries.size() > 0'> " +
            "AND country IN " +
            "<foreach collection='params.countries' item='country' open='(' separator=',' close=')'> " +
            "#{country} " +
            "</foreach> " +
            "</if> " +
            "<if test='params.cities != null and params.cities.size() > 0'> " +
            "AND city IN " +
            "<foreach collection='params.cities' item='city' open='(' separator=',' close=')'> " +
            "#{city} " +
            "</foreach> " +
            "</if> " +
            "<if test='params.industries != null and params.industries.size() > 0'> " +
            "AND industry IN " +
            "<foreach collection='params.industries' item='industry' open='(' separator=',' close=')'> " +
            "#{industry} " +
            "</foreach> " +
            "</if> " +
            "<if test='params.employees != null and params.employees.size() > 0'> " +
            "AND employee IN " +
            "<foreach collection='params.employees' item='employee' open='(' separator=',' close=')'> " +
            "#{employee} " +
            "</foreach> " +
            "</if> " +
            "<if test='params.revenueus != null and params.revenueus.size() > 0'> " +
            "AND reveneu IN " +
            "<foreach collection='params.revenueus' item='reveneu' open='(' separator=',' close=')'> " +
            "#{reveneu} " +
            "</foreach> " +
            "</if> " +
            "</where> " +
            "ORDER BY id " +
            "LIMIT #{params.limit} OFFSET #{params.offset} " +
            "</script>")
    List<Company> searchByConditionWithPagination(@Param("params") Map<String, Object> params);
    
    /**
     * 统计符合条件的记录总数
     */
    @Select("<script>"
          + "SELECT COUNT(1) FROM companies WHERE 1=1 "
          + "<if test='params.companyName != null'> AND company_name LIKE #{params.companyName}</if>"
          + "<if test='params.country != null'> AND country = #{params.country}</if>"
          + "<if test='params.city != null'> AND city = #{params.city}</if>"
          + "<if test='params.industry != null'> AND industry = #{params.industry}</if>"
          + "<if test='params.employee != null'> AND employee = #{params.employee}</if>"
          + "<if test='params.reveneu != null'> AND reveneu = #{params.reveneu}</if>"
          + "</script>")
    long countByCondition(@Param("params") Map<String, Object> params);

    /**
     * 根据条件搜索全球企业数据
     *
     * @param companyName 公司名称
     * @param country 国家
     * @param city 城市
     * @param industry 行业
     * @param subIndustry 子行业
     * @param employee 员工规模
     * @param reveneu 收入规模
     * @param limit 导出数量限制
     * @return 企业列表
     */
    List<Company> searchWithParams(
            @Param("companyName") String companyName,
            @Param("country") String country,
            @Param("city") String city,
            @Param("industry") String industry,
            @Param("subIndustry") String subIndustry,
            @Param("employee") String employee,
            @Param("reveneu") String reveneu,
            @Param("limit") Integer limit
    );

    /**
     * 通过关键词搜索具有电子邮箱的企业或联系人
     * 
     * @param keyword 搜索关键词
     * @param offset 偏移量
     * @param limit 限制数量
     * @return 符合条件的企业或联系人列表
     */
    @Select("<script>" +
            "SELECT id, company_name, first_name, middle_name, last_name, title, country, phone, web_address, email, industry, reveneu " +
            "FROM companies WHERE email IS NOT NULL AND email != '' " +
            "AND (company_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR CONCAT(first_name, ' ', last_name) LIKE CONCAT('%',#{keyword},'%') " +
            "OR web_address LIKE CONCAT('%',#{keyword},'%')) " +
            "ORDER BY id " +
            "LIMIT #{limit} OFFSET #{offset}" +
            "</script>")
    List<Company> searchCompaniesWithEmail(@Param("keyword") String keyword, 
                                          @Param("offset") int offset, 
                                          @Param("limit") int limit);
    
    /**
     * 统计通过关键词搜索具有电子邮箱的企业或联系人数量
     * 
     * @param keyword 搜索关键词
     * @return 符合条件的企业或联系人数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM companies WHERE email IS NOT NULL AND email != '' " +
            "AND (company_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR CONCAT(first_name, ' ', last_name) LIKE CONCAT('%',#{keyword},'%') " +
            "OR web_address LIKE CONCAT('%',#{keyword},'%'))" +
            "</script>")
    long countCompaniesWithEmail(@Param("keyword") String keyword);
    
    /**
     * 通过关键词搜索具有电话的企业或联系人
     * 
     * @param keyword 搜索关键词
     * @param offset 偏移量
     * @param limit 限制数量
     * @return 符合条件的企业或联系人列表
     */
    @Select("<script>" +
            "SELECT id, company_name, first_name, middle_name, last_name, title, country, phone, web_address, email, industry, reveneu " +
            "FROM companies WHERE phone IS NOT NULL AND phone != '' " +
            "AND (company_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR CONCAT(first_name, ' ', last_name) LIKE CONCAT('%',#{keyword},'%') " +
            "OR web_address LIKE CONCAT('%',#{keyword},'%')) " +
            "ORDER BY id " +
            "LIMIT #{limit} OFFSET #{offset}" +
            "</script>")
    List<Company> searchCompaniesWithPhone(@Param("keyword") String keyword, 
                                          @Param("offset") int offset, 
                                          @Param("limit") int limit);
    
    /**
     * 统计通过关键词搜索具有电话的企业或联系人数量
     * 
     * @param keyword 搜索关键词
     * @return 符合条件的企业或联系人数量
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM companies WHERE phone IS NOT NULL AND phone != '' " +
            "AND (company_name LIKE CONCAT('%',#{keyword},'%') " +
            "OR CONCAT(first_name, ' ', last_name) LIKE CONCAT('%',#{keyword},'%') " +
            "OR web_address LIKE CONCAT('%',#{keyword},'%'))" +
            "</script>")
    long countCompaniesWithPhone(@Param("keyword") String keyword);

    /**
     * 根据邮箱精确查询企业或联系人
     * 
     * @param email 邮箱地址
     * @return 匹配的企业或联系人列表
     */
    @Select("SELECT id, company_name, first_name, middle_name, last_name, title, country, phone, web_address, email, industry, reveneu " +
            "FROM companies WHERE email = #{email} LIMIT 10")
    List<Company> findByEmail(@Param("email") String email);

    /**
     * 根据ID查询企业
     * @param id 企业ID
     * @return 企业信息
     */
    @Select("SELECT * FROM companies WHERE id = #{id}")
    Company getCompanyById(@Param("id") Long id);

    /**
     * 批量查询指定表中的企业数据
     * 
     * @param tableName 表名
     * @param idList 记录ID列表
     * @return 企业数据列表
     */
    @Select("<script>"
        + "SELECT * FROM ${tableName} "
        + "WHERE CONCAT(id, IFNULL(CONCAT(':', mt), '')) IN "
        + "<foreach item='item' collection='idList' open='(' separator=',' close=')'>"
        + "  #{item}"
        + "</foreach>"
        + "</script>")
    List<Company> batchQueryByTableAndIds(@Param("tableName") String tableName, @Param("idList") List<String> idList);
}